package com.etc.team2.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.etc.team2.bean.Line;
import com.etc.team2.bean.Station;
import com.etc.team2.jdbc.JDBCHelper;

public class BusDAO {
	/**
	 * 数据库操作
	 */
	private ResultSet set;
	private JDBCHelper helper = null;

	public BusDAO() {
		helper = new JDBCHelper();
	}

	public ResultSet IsManagerExist(String managerName, String password) {// 根据登陆名和密码搜索管理员，如不存在ResultSet=null
		Object[] objects = new Object[] { managerName, password };
		String sql = "select * from bus_manager_info where ManagerName=? and Password=? ";
		set = helper.executeQueryByPrepareStatement(sql, objects);
		try {
			if (set.next()) {
				return set;
			} else {
				return null;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	public List<Station> AllStation() {// 获取所有站台信息，用于管理员管理所有站台
		List<Station> list = new ArrayList<Station>();
		String sql = "select * from bus_station_info";

		set = helper.executeQueryByPrepareStatement(sql, null);
		try {
			while (set.next()) {
				Station sa = new Station();
				List<String> linelist = new ArrayList<String>();
				int id = set.getInt(1);
				String name = set.getString(2);
				String address = set.getString(3);
				sa.setStationId(id);
				sa.setStationName(name);
				sa.setAddress(address);
				sql = "select a.lineName from bus_line_info a,bus_lineandstation_info b where "
						+ "a.lineId=b.lineId and b.stationId=" + id;
				ResultSet set2 = helper.executeQueryByPrepareStatement(sql,
						null);
				while (set2.next()) {
					linelist.add(set2.getString(1));
				}
				sa.setLinelist(linelist);
				list.add(sa);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return list;
	}

	public List<Line> getAllLine() {// 获取所有路线信息，用于管理员管理所有路线。
		List<Line> lines=new ArrayList<Line>(); 
		String sql1="select * from bus_line_info";
		ResultSet set=helper.executeQueryByPrepareStatement(sql1, null);
		try {
			while (set.next()) {
			    Line line=new Line();
				List<String> stationlists=new ArrayList<String>();
				int id=set.getInt(1);
				String lineName=set.getString(2);
				String firsttime=set.getString(3);
				String lasttime=set.getString(4);
				double price=set.getDouble(5);
				line.setLineId(id);
				line.setLineName(lineName);
				line.setFirstTime(firsttime);
				line.setLastTime(lasttime);
				line.setPrice(price);
				String sql2="select b.stationName from bus_lineandstation_info a ,bus_station_info b where a.stationId=b.stationId and a.lineId="+id;
				ResultSet set2=helper.executeQueryByPrepareStatement(sql2, null);
				while (set2.next()) {
					String stationName=set2.getString(1);
					stationlists.add(stationName);
				}
				line.setStationlist(stationlists);
				lines.add(line);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return lines;
	}

	public Station getStation(String stationName) {// 根据站台名获取站台信息，运用在用户搜索指定站台信息时。
		
		String sql = "select stationId,stationName,address from bus_station_info where stationName="
			+"\'"+ stationName+"\'";

		set = helper.executeQueryByPrepareStatement(sql, null);
		try {
			while (set.next()) {
				Station sa = new Station();
				List<String> linelist = new ArrayList<String>();
				int id = set.getInt(1);
				String name = set.getString(2);
				String address = set.getString(3);
				sa.setStationId(id);
				sa.setStationName(name);
				sa.setAddress(address);
				sql = "select a.lineName from bus_line_info a,bus_lineandstation_info b where "
						+ "a.lineId=b.lineId and b.stationId=" + id;
				ResultSet set2 = helper.executeQueryByPrepareStatement(sql,
						null);
				while (set2.next()) {
					linelist.add(set2.getString(1));
				}
				sa.setLinelist(linelist);
				return sa;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return null;
	}
		
		

	public Line getLine(String lineName) {// 根据路线名获取站台信息，运用在用户搜索指定路线信息时。
		String sql="select * from bus_line_info  where lineName="+"\'"+lineName+"\'";
		set=helper.executeQueryByPrepareStatement(sql, null);
		Line line=new Line();
		List<String> stationlist=new ArrayList<String>();
		try {
			while(set.next()){
				int id = set.getInt(1);
				String lineNameString=set.getString(2);
				String firsttime=set.getString(3);
				String lasttime=set.getString(4);
				double price=set.getDouble(5);
				double interval=set.getDouble(6);
				line.setLineName(lineNameString);
				line.setFirstTime(firsttime);
				line.setLastTime(lasttime);
				line.setPrice(price);
				line.setInterval(interval);
				line.setLineId(id);
				String sql2="select b.stationName from bus_lineandstation_info a,bus_station_info b where a.stationId=b.stationId and a.lineId="+
				id;
				ResultSet set2=helper.executeQueryByPrepareStatement(sql2, null);
				while(set2.next()){
					String stationName=set2.getString(1);
					stationlist.add(stationName);
				}
				line.setStationlist(stationlist);
				return line;
			}
				
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
}
